package com.webapp.model;

import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

public class ManagerModel {
	
	private Statement stmt = DatabaseConnector.connect();
	
	/*
	 * creates a new employee object and adds it to the database based on the provided employee object
	 */
	public boolean addEmployee(Employee emp){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM employees WHERE SSN = '" +emp.getSSN()+ "'");
			if(!(rs.next())){
				stmt.executeUpdate("INSERT INTO persons (SSN, Last_Name, First_Name, Address, City, State, Zip_Code, "+
						"Telephone, Email_Address, password) VALUES ('" +emp.getSSN()+ "', '" +emp.getLastName()+ "', '" +
						emp.getFirstName()+ "', '" +emp.getAddress()+ "', '" +emp.getCity()+ "', '" +emp.getState()+ "', '" +
						emp.getZip()+ "', '" +emp.getPhone()+ "', '" +emp.getEmail()+ "', '" +emp.getPassword()+ "')");
				
				DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
				Calendar cal = Calendar.getInstance();
				String date = dateFormat.format(cal.getTime());
				System.out.println(date);
				
				stmt.executeUpdate("INSERT INTO employees (SSN, Start_Date, Hourly_Rate, Manager, Employee_Id) VALUES ('" +emp.getSSN()+
						"', '" +emp.getStartDate()+ "', '" +emp.getHourlyWage()+ "', '" +emp.getManager()+ "', '" +emp.getEmployeeId()+ "')");
				
				return true;
			}
			else
				return false;
			
		}catch(Exception ex){return false;}
	}
	
	/*
	 * Takes in an employee object and upates the database with these new employee fields
	 */
	public boolean editEmployee(Employee emp){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM employees WHERE SSN = '" +emp.getSSN()+ "'");
			if(rs.next()){
				int oldSocial = rs.getInt(1);
				stmt.executeUpdate("UPDATE persons SET SSN = '" +emp.getSSN()+ "', Last_Name = '" +emp.getLastName()+ "', First_Name = '" +emp.getFirstName()+ 
						"', Address = '" +emp.getAddress()+ "', City = '" +emp.getCity()+ "', State = '" +emp.getState()+ "', Zip_Code = '" +emp.getZip()+
						"', Telephone = '" +emp.getPhone()+ "', Email_Address = '" +emp.getEmail()+ "', password = '" +emp.getPassword()+ "' WHERE SSN = '" +oldSocial+ "'");
				stmt.executeUpdate("UPDATE employees SET SSN = '" +emp.getSSN()+ "', Start_Date = '" +emp.getStartDate()+ "', Hourly_Rate = '" +emp.getHourlyWage()+ "', Manager = '" +emp.getManager()+ "', Employee_Id = '" +emp.getEmployeeId()+ "' WHERE SSN = '" +oldSocial+ "'");
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	/*
	 * removes an employee from the database
	 */
	public boolean deleteEmployee(int social){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM employees WHERE SSN = '" +social+ "'");
			if(rs.next()){
				stmt.executeUpdate("DELETE FROM employees WHERE SSN = '" +social+ "'");
				stmt.executeUpdate("DELETE FROM persons WHERE SSN = '" +social+ "'");
				
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	/*
	 * Returns an employee object based on the provided SSN
	 */
	public Employee getEmployee(int social){
		try{
			Statement stmt2 = DatabaseConnector.connect();
			ResultSet rs1 = stmt.executeQuery("SELECT * FROM employees");
			if(rs1.next()){
				Employee e = new Employee(rs1.getInt(1));
				e.setStartDate(rs1.getString(2));
				e.setRate(rs1.getInt(3));
				e.setManager(rs1.getInt(4));
				e.setEmployeeId(rs1.getInt(5));
				
				ResultSet rs2 = stmt2.executeQuery("SELECT * FROM persons WHERE SSN = '" +e.getSSN()+ "'");
				rs2.next();
				e.setLastName(rs2.getString(2));
				e.setFirstName(rs2.getString(3));
				e.setAddress(rs2.getString(4));
				e.setCity(rs2.getString(5));
				e.setState(rs2.getString(6));
				e.setZip(rs2.getInt(7));
				e.setPhone(rs2.getLong(8));
				e.setEmail(rs2.getString(9));
				e.setPassword(rs2.getString(10));

				return e;
			}
			else
				return null;
		}catch(Exception ex){return null;}
	}
	
	public ArrayList<Employee> getAllEmployees(){
		try{
			Statement stmt2 = DatabaseConnector.connect();
			ResultSet rs1 = stmt.executeQuery("SELECT * FROM employees");
			ArrayList<Employee> list = new ArrayList<Employee>();
			while(rs1.next()){
				Employee e = new Employee(rs1.getInt(1));
				e.setStartDate(rs1.getString(2));
				e.setRate(rs1.getInt(3));
				e.setManager(rs1.getInt(4));
				e.setEmployeeId(rs1.getInt(5));
				
				ResultSet rs2 = stmt2.executeQuery("SELECT * FROM persons WHERE SSN = '" +e.getSSN()+ "'");
				rs2.next();
				e.setLastName(rs2.getString(2));
				e.setFirstName(rs2.getString(3));
				e.setAddress(rs2.getString(4));
				e.setCity(rs2.getString(5));
				e.setState(rs2.getString(6));
				e.setZip(rs2.getInt(7));
				e.setPhone(rs2.getLong(8));
				e.setEmail(rs2.getString(9));
				e.setPassword(rs2.getString(10));

				list.add(e);
			}
			return list;
		}catch(Exception ex){return null;}
	}
	
	
	/*
	 * Returns a list of advertisement object that are currently in the database
	 */
	public ArrayList<Advertisement> getItemsBeingAdvertised(){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM advertisement");
			ArrayList<Advertisement> list = new ArrayList<Advertisement>();
			while(rs.next()){
				Advertisement m = new Advertisement();
				m.setAdvertisementId(rs.getInt(1));
				m.setEmployeeId(rs.getInt(2));
				m.setType(rs.getString(3));
				m.setDate(rs.getString(4));
				m.setCompany(rs.getString(5));
				m.setItemName(rs.getString(6));
				m.setContent(rs.getString(7));
				m.setPrice(rs.getInt(8));
				m.setUnits(rs.getInt(9));
				
				list.add(m);
			}
			return list;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * produce a list of transactions by an item name
	 */
	public ArrayList<Sale> getTransactionsByItem(String itemName){
		try{
			ResultSet rs = stmt.executeQuery("SELECT P.* FROM purchases P, advertisement A WHERE A.Advertisement_Id = P.Advertisement AND A.Item_Name = '" +itemName+ "'");
			ArrayList<Sale> list = new ArrayList<Sale>();
			
			while(rs.next()){
				Sale s = new Sale();
				s.setTransactionId(rs.getInt(1));
				s.setDate(rs.getString(2));
				s.setAdvertisementId(rs.getInt(3));
				s.setUnits(rs.getInt(4));
				s.setAccountNumber(rs.getInt(5));
				
				list.add(s);
			}
			
			return list;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Produce a list of transactions by customer name
	 */
	public ArrayList<Sale> getTransactionsByName(String firstName, String lastName){
		try{
			ResultSet rs = stmt.executeQuery("SELECT P.* FROM purchases P, user_has_account H WHERE P.Account = H.Account_Number "+
					"AND H.User_Id IN (SELECT U.User_Id FROM users U, persons S WHERE U.SSN = S.SSN AND S.First_Name = '" +firstName+ "' AND S.Last_Name = '" +lastName+"')");
			ArrayList<Sale> list = new ArrayList<Sale>();
			
			while(rs.next()){
				Sale s = new Sale();
				s.setTransactionId(rs.getInt(1));
				s.setDate(rs.getString(2));
				s.setAdvertisementId(rs.getInt(3));
				s.setUnits(rs.getInt(4));
				s.setAccountNumber(rs.getInt(5));
				
				list.add(s);
			}
			
			return list;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Produce a summary listing of revenue generated by a particular item name
	 */
	public int revenueSummaryByItemName(String itemName){
		try{
			ResultSet rs = stmt.executeQuery("SELECT SUM(A.Unit_Price*P.Number_Of_Units) Revenue FROM purchases P, advertisement A WHERE P.Advertisement = A.Advertisement_Id AND A.Item_Name = '" +itemName+ "'");
			if(rs.next())
				return rs.getInt(1);
			else
				return -1;
			
		}catch(Exception ex){return -1;}
	}
	
	/*
	 * Produce a summary listing of revenue generated by a particular item type
	 */
	public int revenueSummaryByItemType(String itemType){
		try{
			ResultSet rs = stmt.executeQuery("SELECT SUM(A.Unit_Price*P.Number_Of_Units) Revenue FROM purchases P, advertisement A WHERE P.Advertisement = A.Advertisement_Id AND A.Type = '" +itemType+ "'");
			if(rs.next())
				return rs.getInt(1);
			else
				return -1;
			
		}catch(Exception ex){return -1;}
		
	}
	
	/*
	 * Produce a summary listing of revenue generated by a particular customer
	 */
	public int revenueSummaryByCustomer(String firstName, String lastName){
		try{
			ResultSet rs = stmt.executeQuery("SELECT SUM(P.Number_Of_Units*A.Unit_Price) Revenue FROM advertisement A, purchases P WHERE A.Advertisement_Id = P.Advertisement AND P.Account IN "+
					"(SELECT H.Account_Number FROM user_has_account H, users U WHERE H.User_Id = U.User_Id AND U.User_Id IN (SELECT U.User_Id FROM users U, persons S WHERE S.SSN = "+
					"U.SSN AND S.Last_Name = '" +lastName+ "' AND S.First_Name = '" +firstName+ "'))");
			if(rs.next())
				return rs.getInt(1);
			else
				return -1;
		}catch(Exception ex){return -1;}
	}
	
	/*
	 * Produce a list of all items for a company
	 */
	public ArrayList<Advertisement> allItemsByCompany(String companyName){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM advertisement WHERE Company = '" +companyName+ "'");
			ArrayList<Advertisement> list = new ArrayList<Advertisement>();
			
			while(rs.next()){
				Advertisement a = new Advertisement();
				a.setAdvertisementId(rs.getInt(1));
				a.setEmployeeId(rs.getInt(2));
				a.setType(rs.getString(3));
				a.setDate(rs.getString(4));
				a.setCompany(rs.getString(5));
				a.setItemName(rs.getString(6));
				a.setContent(rs.getString(7));
				a.setPrice(rs.getInt(8));
				a.setUnits(rs.getInt(9));
				
				list.add(a);
			}
			return list;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Produce a list of all customers who have purchased a particular item
	 */
	public ArrayList<User> allUsersPurchasedItem(String itemName){
		try{
			ResultSet rs = stmt.executeQuery("SELECT H.User_Id FROM user_has_account H WHERE H.Account_Number IN (SELECT P.Account FROM advertisement A, purchases P WHERE A.Item_Name = '" +itemName+ "')");
			ArrayList<User> list = new ArrayList<User>();
			while(rs.next()){
				ResultSet rs1 = stmt.executeQuery("SELECT * FROM users WHERE User_Id = '" +rs.getInt(1)+ "'");
				rs1.next();
				User u = new User(rs1.getInt(1));
				u.setUserId(rs1.getInt(2));
				u.setCreationDate(rs1.getString(3));
				u.setRating(rs1.getInt(4));
				
				rs1 = stmt.executeQuery("SELECT * FROM persons WHERE SSN = '" +u.getSSN()+ "'");
				rs1.next();
				u.setLastName(rs1.getString(2));
				u.setFirstName(rs1.getString(3));
				u.setAddress(rs1.getString(4));
				u.setCity(rs1.getString(5));
				u.setState(rs1.getString(6));
				u.setZip(rs1.getInt(7));
				u.setPhone(rs1.getLong(8));
				u.setEmail(rs1.getString(9));
				u.setPassword(rs1.getString(10));
			
				list.add(u);
			}
			return list;
		}catch(Exception ex){return null;}
	}
	/*
	 * Produce a list of the most active item (I.e. most units sold)
	 * NOTE: The value for Number_Of_Units in the returned object is the total number (i.e. 5 transactions, 2 per transaction, Number_Of_Units = 10)
	 * 		Then value at index 0 is the most active, while at index n is the least active
	 */
	public ArrayList<Advertisement> mostActiveItems(){
		try{
			ResultSet rs = stmt.executeQuery("SELECT A.Advertisement_Id, A.Employee, A.Type, A.Date, "+
					"A.Company, A.Item_Name, A.Content, A.Unit_Price, SUM(P.Number_Of_Units) as UnitSold  "+
					"FROM advertisement  A  INNER JOIN purchases P ON A.Advertisement_Id = P.Advertisement GROUP BY A.Advertisement_Id ORDER BY UnitSold DESC");
			ArrayList<Advertisement> list = new ArrayList<Advertisement>();
			while(rs.next()){
				Advertisement a = new Advertisement();
				a.setAdvertisementId(rs.getInt(1));
				a.setEmployeeId(rs.getInt(2));
				a.setType(rs.getString(3));
				a.setDate(rs.getString(4));
				a.setCompany(rs.getString(5));
				a.setItemName(rs.getString(6));
				a.setContent(rs.getString(7));
				a.setPrice(rs.getInt(8));
				a.setUnits(rs.getInt(9));
				
				list.add(a);
			}
			return list;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Determine which customer generated the most total revenue 
	 */
	public User customerGenerateMostRevenue(){
		try{
			ResultSet rs = stmt.executeQuery("SELECT U.User_Id, U.Account_Creation_Date, U.SSN, S.Last_Name, S.First_Name, S.Address, S.City, S.State, "+
					"S.Zip_Code, S.Telephone, S.Email_Address, S.password, SUM(P.Number_Of_Units*A.Unit_Price) AS Revenue FROM persons S INNER JOIN users U "+
					"INNER JOIN advertisement A INNER JOIN purchases P INNER JOIN user_has_account H ON A.Advertisement_Id = P.Advertisement AND U.SSN = S.SSN AND "+
					"H.Account_Number = P.Account AND H.User_Id = U.User_Id GROUP BY U.User_Id ORDER BY Revenue DESC");
			User use = null;
			if(rs.next()){
				use = new User(rs.getInt(3));
				use.setUserId(rs.getInt(1));
				use.setCreationDate(rs.getString(2));
				use.setLastName(rs.getString(4));
				use.setFirstName(rs.getString(5));
				use.setAddress(rs.getString(6));
				use.setCity(rs.getString(7));
				use.setState(rs.getString(8));
				use.setZip(rs.getInt(9));
				use.setPhone(rs.getLong(10));
				use.setEmail(rs.getString(11));
				use.setPassword(rs.getString(12));
				use.setRevenue(rs.getInt(13));
			}
			return use;
		}catch(Exception ex){return null;}
	}
	
	//SELECT E.SSN, P2.First_Name, P2.Last_Name,  SUM(P.Number_Of_Units * A.Unit_Price) AS Revenue FROM employees E INNER JOIN advertisement A  INNER JOIN purchases P INNER JOIN persons P2 ON E.SSN = P2.SSN AND E.SSN = A.Employee AND P.Advertisement = A.Advertisement_Id GROUP BY E.Employee_Id
	public Employee customerRepGenerateMostRevenue(){
		try{
			ResultSet rs = stmt.executeQuery("SELECT E.Employee_Id, E.Start_Date, E.Manager, E.Hourly_Rate, E.SSN, S.Last_Name, S.First_Name, S.Address, S.City, S.State, "+
					"S.Zip_Code, S.Telephone, S.Email_Address, S.password, SUM(P.Number_Of_Units*A.Unit_Price) AS Revenue FROM persons S INNER JOIN employees E INNER JOIN "+
					"advertisement A INNER JOIN purchases P ON A.Advertisement_Id = P.Advertisement AND E.SSN = S.SSN AND E.Employee_Id = A.Employee GROUP BY E.Employee_ID ORDER BY Revenue DESC");
			Employee emp = null;
			if(rs.next()){
				emp = new Employee(rs.getInt(5));
				emp.setEmployeeId(rs.getInt(1));
				emp.setStartDate(rs.getString(2));
				emp.setManager(rs.getInt(3));
				emp.setRate(rs.getInt(4));
				emp.setLastName(rs.getString(6));
				emp.setFirstName(rs.getString(7));
				emp.setAddress(rs.getString(8));
				emp.setCity(rs.getString(9));
				emp.setState(rs.getString(10));
				emp.setZip(rs.getInt(11));
				emp.setPhone(rs.getLong(12));
				emp.setEmail(rs.getString(13));
				emp.setPassword(rs.getString(14));
				emp.setRevenue(rs.getInt(15));
			}
			return emp;
		}catch(Exception ex){return null;}
		
	}
	
	public ArrayList<SalesReport> salesReportByMonth(int month){
		try{
			ResultSet rs = stmt.executeQuery("SELECT A.Advertisement_Id, A.Employee, A.Item_Name, SUM(P.Number_Of_Units)AS Total, SUM(P.Number_Of_Units*A.Unit_Price) AS Revenue FROM "+
					"purchases P INNER JOIN advertisement A ON P.Advertisement = A.Advertisement_Id WHERE MONTH(P.Date) = '" +month+ "' GROUP BY A.Advertisement_Id");
			ArrayList<SalesReport> list = new ArrayList<SalesReport>();
			while(rs.next()){
				SalesReport s = new SalesReport();
				s.setAdvertisementId(rs.getInt(1));
				s.setEmployeeId(rs.getInt(2));
				s.setItemName(rs.getString(3));
				s.setTotalNumUnits(rs.getInt(4));
				s.setTotalRevenue(rs.getInt(5));
				
				list.add(s);
			}
			return list;
		}catch(Exception ex){return null;}
	}
}
